package DAO;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import DBConn.DBConn;
import MySQL.Sales;

public class SalesDaoImpl implements SalesDao{
	//增加
	public void add(Sales s) {
		String ssql = "insert into sales values(?,?,?,?)";
		Connection conn = DBConn.open();
		try {
			PreparedStatement pstmt = conn.prepareStatement(ssql);
			pstmt.setString(1,s.getId());
			pstmt.setInt(2,s.getNumber());
			pstmt.setInt(3,s.getPrice());
			pstmt.setDate(4,s.getDate());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBConn.close(conn);
		}
	}
	//删除
	public void delete(String id) {
		String ssql = "delete from sales where id=?";
		Connection conn = DBConn.open();
		try {
			PreparedStatement pstmt = conn.prepareStatement(ssql);
			pstmt.setString(1,id);
			pstmt.executeUpdate();
		} catch (SQLException e){
			e.printStackTrace();
		}finally{
			DBConn.close(conn);
		}
	}
	//通过id得到商品
	public Sales getSalesById(String id) {
		String ssql = "select * from sales where id=?";
		Connection conn = DBConn.open();
		try {
			PreparedStatement pstmt = conn.prepareStatement(ssql);
			pstmt.setString(1, id);
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				int number = rs.getInt(2);
				int price = rs.getInt(3);
				Date date = rs.getDate(4);
				Sales s = new Sales();
				s.setId(id);
				s.setNumber(number);
				s.setPrice(price);
				s.setDate(date);
				return s;
			}
		} catch (SQLException e){
			e.printStackTrace();
		}finally{
			DBConn.close(conn);
		}	
		return null;
	}
	//查询
	public String query() {
		String ssql = "select * from sales";
		Connection conn = DBConn.open();
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(ssql);
			String s ="";
			while(rs.next()){
				String id = rs.getString(1);
				int number = rs.getInt(2);
				int price = rs.getInt(3);
				Date date = rs.getDate(4);
				s = s+id+"   "+number+"   "+price+"   "+date+"\n";
			}
				return s;
		}catch (SQLException e1){
			e1.printStackTrace();
		}finally{
			DBConn.close(conn);
		}
		return null;
	}
}
